﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Reflection;
using System.IO;
using System.Collections;
using System.Runtime.InteropServices;
using System.Data.OleDb;
using System.Data;
using System.Data.Common;
using PlanningGIS.Util.Data;
using ADOX;

namespace PlanningGIS.Access
{    
    /// <summary>
    /// Access数据库操作
    /// </summary>
    public class AccessContext : DbContext, IDbContext
    {
        /// <summary>
        /// 构造函数
        /// </summary>
        public AccessContext()
        {
            AutoClosed = true;
        }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="fullname">access数据库文件全路径</param>
        public AccessContext(string fullname)
            : this()
        {
            this.FullName = fullname;
        }
        
        /// <summary>
        /// access数据库文件全路径
        /// </summary>
        private string _FullName = "";

        /// <summary>
        /// 获取或设置access数据库文件全路径
        /// </summary>
        public string FullName
        {
            get { return _FullName; }
            set
            {
                _FullName = value;
                _ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + this.FullName.Trim();
            }
        }
        
        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        private string _ConnectionString = "";

        /// <summary>
        /// 获取或设置数据库连接字符串
        /// </summary>
        public override string ConnectionString
        {
            get { return _ConnectionString; }
            set
            {
                _ConnectionString = value.Trim();
                int index = _ConnectionString.ToLower().IndexOf("data source=");
                if (index < 0)
                {
                    _ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + value.Trim();
                    _FullName = value.Trim();
                }
                else
                {
                    string str = ConnectionString.Remove(0, index + "Data Source=".Length).Trim();
                    index = str.ToLower().IndexOf(".mdb");
                    _FullName = str.Substring(0, index + ".mdb".Length);
                }
            }
        }

        /// <summary>
        /// 是否可以连接到数据库
        /// </summary>
        /// <returns></returns>
        public bool CanConncect()
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) return false;
            try
            {
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                if (this.Connection != null)
                {
                    this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 通过DAO创建数据表
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="fields">字段</param>
        /// <returns></returns>
        public bool CreateTable(string tableName, List<DbFieldInfo> fields)
        {
            Catalog catalog = new Catalog();
            ADODB.Connection cn = new ADODB.Connection();
            try
            {
                cn.Open(ConnectionString, null, null, -1);
                catalog.ActiveConnection = cn;
                ADOX.Table table = new ADOX.Table();
                table.Name = tableName;
                foreach (DbFieldInfo f in fields)
                {
                    DataTypeEnum datatype = DataTypeEnum.adVarWChar;
                    int len = f.Length;
                    switch (f.TypeName.ToLower())
                    {
                        case "int":
                            datatype = DataTypeEnum.adInteger;
                            len = 9;
                            break;
                        case "long":
                            datatype = DataTypeEnum.adInteger;
                            len = 9;
                            break;
                        case "double":
                            datatype = DataTypeEnum.adDouble;
                            len = 9;
                            break;
                        case "datetime":
                            datatype = DataTypeEnum.adDate;
                            len = 0;
                            break;
                        //case "boolean": datatype = DataTypeEnum.adBoolean; break;
                        case "byte":
                            datatype = DataTypeEnum.adVarBinary; //二进制
                            len = 0;
                            break;
                        case "byte[]":
                            datatype = DataTypeEnum.adLongVarBinary; //OLE对象
                            len = 0;
                            break;
                        case "string":
                            if (f.Length == 0 || f.Length > 255)
                            {
                                datatype = DataTypeEnum.adLongVarWChar;//备注
                                len = 255;
                            }
                            else
                            {
                                datatype = DataTypeEnum.adVarWChar;
                            }
                            break;
                        default: datatype = DataTypeEnum.adVarWChar; break;
                    }
                    ADOX.Column col = new Column();
                    col.Type = datatype;
                    col.ParentCatalog = catalog;
                    col.Name = f.Name;
                    col.Properties["Nullable"].Value = true;
                    //col.Properties["Jet OLEDB:Allow Zero Length"].Value = true;
                    table.Columns.Append(col, datatype, len);
                }
                catalog.Tables.Append(table);
                return true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cn != null)
                    cn.Close();
                cn = null;
                catalog = null;
                GC.Collect();
            }
        }

        /// <summary>
        /// 添加表字段
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="fields">字段</param>
        /// <returns></returns>
        public void AddFields(string tableName, List<DbFieldInfo> fields)
        {
            if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("数据表名称不能为空");
            if (!ExistsObject(tableName, zjgisDbObjectType.zjgisDbObjectTable)) throw new Exception("数据表不存在");
            List<DbFieldInfo> tableFields = GetFieldInfos(tableName);
            string sql = "alter table " + tableName + " add ";
            foreach (DbFieldInfo f in fields)
            {
                if (tableFields.Find(t => t.Name.ToUpper() == f.Name.ToUpper()) != null) continue;
                string datatype = "";
                switch (f.TypeName.ToLower())
                {
                    case "int":
                    case "long": datatype = zjgisMdbFieldType.INTEGER.ToString(); break;
                    case "double": datatype = zjgisMdbFieldType.FLOAT.ToString(); break;
                    case "datetime": datatype = zjgisMdbFieldType.DATETIME.ToString(); break;
                    case "boolean": datatype = zjgisMdbFieldType.BIT.ToString(); break;
                    case "byte": datatype = zjgisMdbFieldType.BINARY.ToString(); break;
                    case "byte[]": datatype = zjgisMdbFieldType.IMAGE.ToString(); break;
                    case "string":
                        if (f.Length == 0 || f.Length > 255)
                            datatype = zjgisMdbFieldType.TEXT.ToString();
                        else
                            datatype = zjgisMdbFieldType.TEXT.ToString() + "(" + f.Length + ")";
                        break;
                    default: datatype = zjgisMdbFieldType.TEXT.ToString() + "(" + f.Length + ")"; break;
                }
                sql += f.Name.ToUpper() + " " + datatype;
                if (!f.Nullable) sql += " not null";
                sql += ",";
                tableFields.Add(f);
            }
            sql = sql.Trim().Substring(0, sql.Trim().Length - 1);
            try
            {
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OleDbCommand cmd = new OleDbCommand(sql, (this.Connection as OleDbConnection)))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.Connection != null) this.Connection.Close();
                this.Connection = null;
            }
        }

        /// <summary>
        /// 通过SQL语句创建数据表
        /// </summary>
        /// <param name="tableName">数据表名称</param>
        /// <param name="fields">字段</param>
        public void CreateTable2(string tableName, List<DbFieldInfo> fields)
        {
            if (string.IsNullOrWhiteSpace(tableName)) throw new Exception("数据表名称不能为空");
            if (ExistsObject(tableName, zjgisDbObjectType.zjgisDbObjectTable)) return ;
            string sql = "create table " + tableName + " ( ";
            foreach (DbFieldInfo f in fields)
            {
                string datatype = "";
                switch (f.TypeName.ToLower())
                {
                    case "int":
                    case "long": datatype = zjgisMdbFieldType.INTEGER.ToString(); break;
                    case "double": datatype = zjgisMdbFieldType.FLOAT.ToString(); break;
                    case "datetime": datatype = zjgisMdbFieldType.DATETIME.ToString(); break;
                    case "boolean": datatype = zjgisMdbFieldType.BIT.ToString(); break;
                    case "byte": datatype = zjgisMdbFieldType.BINARY.ToString(); break;
                    case "byte[]": datatype = zjgisMdbFieldType.IMAGE.ToString(); break;
                    case "string":
                        if (f.Length == 0 || f.Length > 255)
                            datatype = zjgisMdbFieldType.TEXT.ToString();
                        else
                            datatype = zjgisMdbFieldType.TEXT.ToString() + "(" + f.Length + ")";
                        break;
                    default: datatype = zjgisMdbFieldType.TEXT.ToString() + "(" + f.Length + ")"; break;
                }
                sql += f.Name.ToLower() + " " + datatype;
                if (!f.Nullable) sql += " not null";                
                sql += ",";
            }
            sql = sql.Trim().Substring(0, sql.Trim().Length - 1) + " )";
            try
            {
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OleDbCommand cmd = new OleDbCommand(sql, (this.Connection as OleDbConnection)))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.Connection != null) this.Connection.Close();
                this.Connection = null;
            }
        }
        
        /// <summary>
        /// 获取用户对象列表
        /// </summary>
        /// <param name="type">用户对象类型</param>
        /// <returns></returns>
        public List<string> GetUserObjects(zjgisDbObjectType type = zjgisDbObjectType.zjgisDbObjectTable)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) throw new Exception("连接字符串不能为空。");
            try
            {
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (Connection.State != ConnectionState.Open) Connection.Open();
                return _GetUserObjects(type);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                    {
                        this.Connection.Close();
                    }
                }
            }
        }
        
        /// <summary>
        /// 获取用户对象列表,并转为大写
        /// </summary>
        /// <param name="type">用户对象类型</param>
        /// <returns></returns>
        private List<string> _GetUserObjects(zjgisDbObjectType type)
        {
            object[] restrictions = null;
            Guid schema = OleDbSchemaGuid.Tables;
            if (type == zjgisDbObjectType.zjgisDbObjectTable)
            {
                schema = OleDbSchemaGuid.Tables;
                restrictions = new object[] { null, null, null, "TABLE" };
            }
            else if (type == zjgisDbObjectType.zjgisDbObjectProcedure) schema = OleDbSchemaGuid.Procedures;
            else if (type == zjgisDbObjectType.zjgisDbObjectView) schema = OleDbSchemaGuid.Views;
            DataTable dt = (this.Connection as OleDbConnection).GetOleDbSchemaTable(schema, restrictions);
            return dt.AsEnumerable().Select(t => t.Field<string>("table_name").ToString().ToUpper()).ToList();
        }
        
        /// <summary>
        /// 获取MDB数据库中的所有数据表名,并转为大写
        /// </summary>
        /// <returns></returns>
        public List<string> GetTableNames()
        {
            return GetUserObjects(zjgisDbObjectType.zjgisDbObjectTable);
        }

        /// <summary>
        /// 根据数据表名称，获取表中的字段信息 COLUMN_NAME
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <returns></returns>
        public System.Data.DataTable GetTableFields(string tablename)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) throw new Exception("连接字符串不能为空。");               
            try
            {
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (Connection.State != ConnectionState.Open) Connection.Open();
                DataTable table = (this.Connection as OleDbConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new string[] { null, null, tablename.Trim(), null });
                if (table == null || table.Rows.Count == 0) return null;
                DataTable dtCopy = table.Copy();
                DataView dv = table.DefaultView;
                dv.Sort = "ORDINAL_POSITION ASC";
                dtCopy = dv.ToTable();
                return dtCopy;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                    {
                        this.Connection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 获取数据表的字段信息
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <returns></returns>
        public List<DbFieldInfo> GetFieldInfos(string tablename)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) throw new Exception("连接字符串不能为空。");
            try
            {
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (Connection.State != ConnectionState.Open) Connection.Open();
                DataTable table = (this.Connection as OleDbConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new string[] { null, null, tablename.Trim(), null });
                if (table == null || table.Rows.Count == 0) return null;
                //table.DefaultView.Sort = "ORDINAL_POSITION ASC";
                DataTable dtCopy = table.Copy();
                DataView dv = table.DefaultView;
                dv.Sort = "ORDINAL_POSITION ASC";
                dtCopy = dv.ToTable();
                List<DbFieldInfo> fields = new List<DbFieldInfo>();
                foreach (DataRow dr in dtCopy.Rows)
                {
                    DbFieldInfo f = new DbFieldInfo()
                    {
                        Name = dr["COLUMN_NAME"].ToString(),
                        Nullable = bool.Parse(dr["IS_NULLABLE"].ToString())                        
                    };
                    f.Length = dr["CHARACTER_OCTET_LENGTH"] == DBNull.Value ? 0 : int.Parse(dr["CHARACTER_OCTET_LENGTH"].ToString())/2;
                    f.Description = dr["DESCRIPTION"] == DBNull.Value ? "" : dr["DESCRIPTION"].ToString();
                    int type = int.Parse(dr["DATA_TYPE"].ToString());    //字段类型
                    f.OriginalTypeName = type.ToString();
                    switch (type)
                    {
                        case 2: f.TypeName = "Int"; break;
                        case 3: f.TypeName = "Int"; break;
                        case 4: f.TypeName = "Single"; break;
                        case 5: f.TypeName = "Double"; break;
                        case 6: f.TypeName = "Double"; break;
                        case 7: f.TypeName = "DateTime"; break;
                        case 11: f.TypeName = "Boolean"; break;
                        case 17: f.TypeName = "Byte"; break;
                        case 72: f.TypeName = "String"; break;
                        case 128: f.TypeName = "Byte[]"; break;
                        case 130: f.TypeName = "String"; break;
                        case 131: f.TypeName = "Double"; break;
                        default: f.TypeName = "String"; break;
                    }
                    fields.Add(f);
                };
                table = (this.Connection as OleDbConnection).GetOleDbSchemaTable(OleDbSchemaGuid.Primary_Keys, null);    //主键
                foreach (DataRow row in table.Rows)
                {
                    if (tablename.Equals(row["TABLE_NAME"].ToString(), StringComparison.CurrentCultureIgnoreCase))
                    {
                        DbFieldInfo f = fields.FirstOrDefault(t => t.Name.Equals(row["COLUMN_NAME"].ToString(), StringComparison.CurrentCultureIgnoreCase));
                        if (f != null && !string.IsNullOrWhiteSpace(f.Name))
                            f.IsKey = true;
                    }
                }
                return fields;
            }
            catch (Exception ex)
            {
                Message = "获取字段出错，有可能是数据表不存在:" + ex.Message;
                return null;
            }
            finally
            {
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                    {
                        this.Connection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="tablename">要查询的数据表</param>
        /// <param name="where">不带where的条件语句</param>
        /// <param name="filed">要统计的字段</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns></returns>
        public long GetCount(string tablename, string where, string filed = "*", Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            return GetCountParameter(tablename, where, filed, ConvertParameter(parameters), cmdtype);
        }

        /// <summary>
        /// 获取记录总数
        /// </summary>
        /// <param name="tablename">要查询的数据表</param>
        /// <param name="where">不带where的条件语句</param>
        /// <param name="filed">要统计的字段</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns></returns>
        public long GetCountParameter(string tablename, string where, string filed = "*", OleDbParameter[] parameters = null, CommandType cmdtype = CommandType.Text)
        {
            string sql = "select count(" + filed + ") from " + tablename;
            if (!string.IsNullOrEmpty(where) && !string.IsNullOrWhiteSpace(where))
                sql += " where " + where;
            ConvertParamater(ref sql, ref parameters);
            DataTable dt = GetDataTableParameter(sql, parameters, cmdtype);
            if (dt == null || dt.Rows.Count == 0) return 0;
            return long.Parse(dt.Rows[0][0].ToString());
        }

        /// <summary>
        /// 获取字段的唯一值
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="fieldname">字段名称</param>
        /// <returns></returns>
        public DataTable GetUniqueValue(string tablename, string fieldname)
        {
            return GetDataTableParameter("select distinct " + fieldname + " from " + tablename + " order by " + fieldname);
        }

        /// <summary>
        /// 执行Sql查询语句，获取满足条件的数据表信息
        /// </summary>
        /// <param name="sql">要执行的Sql语句,或存储过程名称</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>System.Data.DataTable</returns>
        public override DataTable GetDataTable(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            return GetDataTableParameter(sql, ConvertParameter(parameters), cmdtype);
        }

        /// <summary>
        /// 执行Sql查询语句，获取满足条件的数据表信息
        /// </summary>
        /// <param name="sql">要执行的Sql语句,或存储过程名称</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>System.Data.DataTable</returns>
        public DataTable GetDataTableParameter(string sql, OleDbParameter[] parameters = null, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
                throw new Exception("连接字符串不能为空。");
            try
            {
                ConvertParamater(ref sql, ref parameters);
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                using (OleDbCommand cmd = new OleDbCommand() { CommandText = sql, CommandType = cmdtype, Connection = (this.Connection as OleDbConnection) })
                {
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);
                    using (OleDbDataAdapter da = new OleDbDataAdapter(cmd))
                    {
                        if (this.Connection.State != ConnectionState.Open)
                            this.Connection.Open();
                        DataTable dt = new DataTable();
                        da.Fill(dt);
                        cmd.Parameters.Clear();
                        return dt;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                    {
                        this.Connection.Close();
                    }
                }
            }

        }

        /// <summary>
        /// 执行Sql语句或存储过程，过去满足条件的数据集记录。注意：数据使用完成后要关闭数据集
        /// </summary>
        /// <param name="sql">要执行的Sql语句,或存储过程名称</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns></returns>
        public IDataReader GetDataReader(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            return GetDataReaderParameter(sql, ConvertParameter(parameters), cmdtype);
        }

        /// <summary>
        /// 执行Sql语句或存储过程，过去满足条件的数据集记录。注意：数据使用完成后要关闭数据集
        /// </summary>
        /// <param name="sql">要执行的Sql语句,或存储过程名称</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns></returns>
        public IDataReader GetDataReaderParameter(string sql, OleDbParameter[] parameters = null, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
                throw new Exception("连接字符串不能为空。");
            try
            {
                ConvertParamater(ref sql, ref parameters);
                if (this.Connection == null)
                    this.Connection = new OleDbConnection(ConnectionString);
                OleDbCommand cmd = new OleDbCommand() { CommandText = sql, CommandType = cmdtype, Connection = (this.Connection as OleDbConnection) };
                if (parameters != null && parameters.Length > 0)
                    cmd.Parameters.AddRange(parameters);
                if (this.Connection.State != ConnectionState.Open)
                    this.Connection.Open();
                OleDbDataReader reader = cmd.ExecuteReader();
                cmd.Parameters.Clear();
                return reader;
            }
            catch (Exception ex)
            {
                Close();
                throw ex;
            }
            finally
            {
                
            }
        }

        /// <summary>
        /// 执行查询，返回查询结果中第一行的第一列。
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>查询结果中第一行的第一列</returns>
        public object GetScalar(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            return GetScalarParameter(sql, ConvertParameter(parameters), cmdtype);
        }

        /// <summary>
        /// 执行查询，返回查询结果中第一行的第一列。
        /// </summary>
        /// <param name="sql">查询语句</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>查询结果中第一行的第一列</returns>
        public object GetScalarParameter(string sql, OleDbParameter[] parameters = null, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
                throw new Exception("连接字符串不能为空。");
            try
            {
                ConvertParamater(ref sql, ref parameters);
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OleDbCommand cmd = new OleDbCommand() { CommandText = sql, CommandType = cmdtype, Connection = (this.Connection as OleDbConnection) })
                {
                    if (parameters != null && parameters.Length > 0)
                        cmd.Parameters.AddRange(parameters);

                    object o = cmd.ExecuteScalar();
                    cmd.Parameters.Clear();
                    return o;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.AutoClosed && Transaction == null)
                //if (this.AutoClosed)
                {
                    if (this.Connection != null)
                    {
                        this.Connection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 自动拼接分页查询的Sql，执行分页查询
        /// </summary>
        /// <param name="sqlTable">数据表、视图名称;或不带排序语句的完整的SQL查询语句</param>
        /// <param name="order">带order by 的排序条件，asc不能省略</param>
        /// <param name="pageIndex">当前页:从1开始</param>
        /// <param name="pageSize">每页显示的记录条数，大于0的整数</param>
        /// <param name="total">sqlTable中的总记录条数，大于0的整数</param>
        /// <returns></returns>
        public System.Data.DataTable GetPages(string sqlTable, string order, long pageIndex, long pageSize, long total)
        {
            if (total < 1) throw new Exception("请先获取满足条件的记录总数");
            long rowcount = pageSize * pageIndex;
            long rownum = pageSize;
            if (total < rowcount)
            {
                rownum = total - pageSize * (pageIndex - 1);
            }
            string ordertemp = order.Trim();
            if (ordertemp.Trim() != "")
            {
                ordertemp = ordertemp.Trim().Replace("asc", "ascc");
                ordertemp = ordertemp.Trim().Replace("desc", "asc");
                ordertemp = ordertemp.Trim().Replace("ascc", "desc");
            }
            string sql = " select top " + rownum + " a.*  ";
            sql += " from ( ";
            sql += "   select top " + rowcount + " b.*  ";
            if (sqlTable.Contains("select"))
                sql += "     from ( " + sqlTable + ") b ";
            else
                sql += "     from  " + sqlTable + " b ";
            sql += "      " + ordertemp;
            sql += " ) a ";
            sql += order;
            return GetDataTable(sql);
        }

        /// <summary>
        /// 获取数据库系统时间
        /// </summary>
        /// <returns></returns>
        public DateTime GetSysDateTime()
        {
            return DateTime.Now;
        }

        /// <summary>
        /// 创建事务对象，事务执行完成后注意要提交事务，并关闭数据库
        /// </summary>
        public override void CreateTransaction()
        {
            if (Transaction != null) RollbackTransaction();
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) throw new Exception("连接字符串不能为空。");
            if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
            if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
            AutoClosed = false;
            Transaction = (this.Connection as OleDbConnection).BeginTransaction(IsolationLevel.ReadCommitted);
        }

        /// <summary>
        /// 创建数据操作命令
        /// </summary>
        /// <returns></returns>
        public IDbCommand CreateCommand()
        {
            return new OleDbCommand() { Connection = (this.Connection as OleDbConnection), Transaction = (Transaction as OleDbTransaction) };
        }
        
        /// <summary>
        /// 执行 SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="sql">SQL 语句。</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype"></param>
        /// <returns>受影响的行数。</returns>
        public override int ExecuteNonQuery(string sql, Dictionary<string, dynamic> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            return ExecuteNonQueryParameter(sql, ConvertParameter(parameters), cmdtype);
        }

        /// <summary>
        /// 在数据库事务中将一条 SQL 语句执行多次。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="cmdtype"></param>
        /// <returns>影响的记录条数。</returns>
        public override int ExecuteNonQuery(string sql, List<Dictionary<string, dynamic>> parameters, CommandType cmdtype = CommandType.Text)
        {
            List<OleDbParameter[]> ps = new List<OleDbParameter[]>();
            foreach (Dictionary<string, dynamic> itm in parameters)
            {
                if (itm != null && itm.Count > 0)
                    ps.Add(ConvertParameter(itm));
            }
            return ExecuteNonQueryParameter(sql, ps, cmdtype);
        }

        /// <summary>
        /// 在数据库事务中执行多条 SQL 语句。
        /// </summary>
        /// <param name="sqls">多条 SQL 语句</param>
        /// <param name="parameters"></param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>影响的记录条数。</returns>
        public override int ExecuteNonQuery(List<string> sqls, List<Dictionary<string, dynamic>> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            List<OleDbParameter[]> ps = null;
            if (parameters != null && parameters.Count > 0)
            {
                ps = new List<OleDbParameter[]>();
                foreach (Dictionary<string, dynamic> itm in parameters)
                {
                    if (itm != null && itm.Count > 0)
                        ps.Add(ConvertParameter(itm));
                    else
                        ps.Add(null);
                }
            }
            return ExecuteNonQueryParameter(sqls, ps, cmdtype);
        }

        /// <summary>
        /// 执行 SQL 语句并返回受影响的行数。
        /// </summary>
        /// <param name="sql">SQL 语句。</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <param name="cmdtype"></param>
        /// <returns>受影响的行数。</returns>
        public int ExecuteNonQueryParameter(string sql, OleDbParameter[] parameters = null, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
            {
                Message = "连接字符串不能为空。";
                return -1;
            }
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            int n = 0;
            try
            {
                ConvertParamater(ref sql, ref parameters);
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OleDbCommand cmd = new OleDbCommand(sql, (this.Connection as OleDbConnection)))
                {
                    if (Transaction != null) cmd.Transaction = Transaction as OleDbTransaction;
                    cmd.CommandType = cmdtype;
                    if (parameters != null && parameters.Length > 0) cmd.Parameters.AddRange(parameters);
                    n = cmd.ExecuteNonQuery();
                    cmd.Parameters.Clear();
                    return n;
                }
            }
            catch (Exception ex)
            {
                if (isAutoTran) RollbackTransaction();
                throw new Exception("执行Sql【" + sql + "】失败：" + ex.ToString());
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 在数据库事务中将一条 SQL 语句执行多次。
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        /// <param name="cmdtype"></param>
        /// <returns>影响的记录条数。</returns>
        public int ExecuteNonQueryParameter(string sql, List<OleDbParameter[]> parameters, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
            {
                Message = "连接字符串不能为空。";
                return -1;
            }
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            int n = 0;
            try
            {
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OleDbCommand cmd = new OleDbCommand() { Connection = (this.Connection as OleDbConnection) })
                {
                    if (Transaction != null) cmd.Transaction = Transaction as OleDbTransaction;
                    cmd.CommandType = cmdtype;
                    foreach (OleDbParameter[] p in parameters)
                    {
                        string str = sql;
                        OleDbParameter[] ps = p;
                        ConvertParamater(ref str, ref ps);
                        if (p != null && p.Length > 0)
                        {
                            cmd.Parameters.AddRange(ps);
                        }
                        cmd.CommandText = str;
                        n += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                }
                return n;
            }
            catch (Exception ex)
            {
                if (isAutoTran) RollbackTransaction();
                throw new Exception("执行Sql【" + sql + "】失败：" + ex.ToString());
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 在数据库事务中执行多条 SQL 语句。
        /// </summary>
        /// <param name="sqls">多条 SQL 语句</param>
        /// <param name="parameters"></param>
        /// <param name="cmdtype">命令类型</param>
        /// <returns>影响的记录条数。</returns>
        public int ExecuteNonQueryParameter(List<string> sqls, List<OleDbParameter[]> parameters = null, CommandType cmdtype = CommandType.Text)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
            {
                Message = "连接字符串不能为空。";
                return -1;
            }
            if (sqls == null || sqls.Count == 0)
            {
                Message = "要执行的Sql不能为空。";
                return -1;
            }
            if (parameters != null && sqls.Count != parameters.Count)
            {
                Message = "要执行的Sql总数与参数总数不一致。";
                return -1;
            }
            string sql = "";
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            int n = 0;
            try
            {
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OleDbCommand cmd = new OleDbCommand() { Connection = (this.Connection as OleDbConnection), CommandType = cmdtype })
                {
                    if (Transaction != null) cmd.Transaction = Transaction as OleDbTransaction;
                    for (int i = 0; i < sqls.Count; i++)
                    {
                        sql = sqls[i];
                        if (parameters != null && parameters[i] != null && parameters[i].Length > 0)
                        {
                            OleDbParameter[] ps = parameters[i];
                            ConvertParamater(ref sql, ref ps);
                            cmd.Parameters.AddRange(ps);
                        }
                        else
                        {
                            OleDbParameter[] ps = null ;
                            ConvertParamater(ref sql, ref ps);
                        }
                        cmd.CommandText = sql;
                        n += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                }
                return n;
            }
            catch (Exception ex)
            {
                if (isAutoTran) RollbackTransaction();
                throw new Exception("执行Sql【" + sql + "】失败：" + ex.ToString());
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 判断符合条件的记录是否存在。
        /// </summary>
        /// <param name="sql">SQL 语句。</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <returns>符合条件的记录是否存在。</returns>
        public bool Exists(string sql, Dictionary<string, dynamic> parameters = null)
        {
            DataTable dt = GetDataTableParameter(sql, ConvertParameter(parameters));
            if (dt == null || dt.Rows.Count == 0) return false;
            return true;
        }

        /// <summary>
        /// 判断符合条件的记录是否存在。
        /// </summary>
        /// <param name="sql">SQL 语句。</param>
        /// <param name="parameters">参数信息，使用like时参数的值为'%值%'</param>
        /// <returns>符合条件的记录是否存在。</returns>
        public bool ExistsParameter(string sql, OleDbParameter[] parameters = null)
        {
            DataTable dt = GetDataTableParameter(sql, parameters);
            if (dt == null || dt.Rows.Count == 0) return false;
            return true;
        }
        
        /// <summary>
        /// 是否存在指定的用户对象类型
        /// </summary>
        /// <param name="name">用户对象名称，表名、视图名等</param>
        /// <param name="type">对象类型</param>
        /// <returns></returns>
        public bool ExistsObject(string name, zjgisDbObjectType type = zjgisDbObjectType.zjgisDbObjectTable)
        {
            List<string> names = new List<string>();
            if (type == zjgisDbObjectType.zjgisDbObjectTable) names = GetTableNames();
            else if (type == zjgisDbObjectType.zjgisDbObjectProcedure) names = new List<string>();
            else if (type == zjgisDbObjectType.zjgisDbObjectView) names = new List<string>();
            if (names == null || names.Count == 0) return false;
            return names.Exists(t => t.Equals(name, StringComparison.CurrentCultureIgnoreCase));
        }

        /// <summary>
        /// 在每个数据表中插入多条指定的数据
        /// </summary>
        /// <param name="tablenames">要插入到的数据表</param>
        /// <param name="parameters">要新增的值</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        public override int Insert(List<string> tablenames, List<List<Dictionary<string, dynamic>>> parameters, bool checkfield = true)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) throw new Exception("连接字符串不能为空。");
            if (tablenames == null || tablenames.Count == 0) throw new Exception("数据表名称不能为空。");
            if (parameters != null && tablenames.Count != parameters.Count) throw new Exception("数据表与记录总数不一致。");
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            int n = 0;
            string sql = "";
            Dictionary<string, DataTable> outFields = null;
            if (checkfield)
            {
                outFields = new Dictionary<string, DataTable>();
                foreach (string s in tablenames)
                {
                    outFields.Add(s.ToUpper().Trim(), GetTableFields(s));
                }
            }
            try
            {
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OleDbCommand cmd = new OleDbCommand() { Connection = (this.Connection as OleDbConnection), CommandType =  CommandType.Text })
                {
                    cmd.Transaction = Transaction as OleDbTransaction;
                    for (int i = 0; i < tablenames.Count; i++)
                    {
                        foreach (Dictionary<string, dynamic> p in parameters[i])
                        {
                            DataTable dt = null;
                            if (outFields != null) dt = outFields[tablenames[i].Trim().ToUpper()];
                            OleDbParameter[] ps = GetInsertParameters(tablenames[i], p, out sql, dt);
                            if (ps == null || ps.Length == 0) continue;
                            cmd.Parameters.AddRange(ps);
                            cmd.CommandText = sql;
                            n += cmd.ExecuteNonQuery();
                            cmd.Parameters.Clear();
                        }
                    }
                }
                return n;
            }
            catch (Exception ex)
            {
                Message = "当前执行的Sql：" + sql;
                if (isAutoTran) RollbackTransaction();
                throw ex;
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 更新多个数据表中满足条件的记录
        /// </summary>
        /// <param name="tablenames">要更新的数据表集合</param>
        /// <param name="values">更新的值</param>
        /// <param name="wheres">满足的条件</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        public override int Update(List<string> tablenames, List<Dictionary<string, dynamic>> values, List<Dictionary<string, dynamic>> wheres, bool checkfield = true)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) throw new Exception("连接字符串不能为空。");
            if (tablenames == null || tablenames.Count == 0) throw new Exception("数据表名称不能为空。");
            if (values != null && tablenames.Count != values.Count) throw new Exception("数据表与记录总数不一致。");
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            int n = 0;
            string sql = "";
            Dictionary<string, DataTable> outFields = null;
            if (checkfield)
            {
                outFields = new Dictionary<string, DataTable>();
                foreach (string s in tablenames)
                {
                    outFields.Add(s.ToUpper().Trim(), GetTableFields(s));
                }
            }
            try
            {
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OleDbCommand cmd = new OleDbCommand() { Connection = (this.Connection as OleDbConnection), CommandType = CommandType.Text })
                {
                    cmd.Transaction = Transaction as OleDbTransaction;
                    for (int i = 0; i < tablenames.Count; i++)
                    {
                        DataTable dt = null;
                        if (outFields != null) dt = outFields[tablenames[i].Trim().ToUpper()];
                        OleDbParameter[] ps = GetUpdateValues(tablenames[i], values[i], out sql, dt);
                        if (ps == null || ps.Length == 0) continue;
                        string where = "";
                        OleDbParameter[] pw = GetUpdateWheres(wheres[i], out where, dt);
                        if (pw != null && pw.Length > 0)
                        {
                            ps = ps.Union(pw).ToArray();
                            sql += " " + where;
                        }  
                        cmd.Parameters.AddRange(ps);
                        cmd.CommandText = sql;
                        n += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                }
                return n;
            }
            catch (Exception ex)
            {
                Message = "当前执行的Sql：" + sql;
                if (isAutoTran) RollbackTransaction();
                throw ex;
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 更新多个数据表中满足条件的记录
        /// </summary>
        /// <param name="tablenames">要更新的数据表集合</param>
        /// <param name="values">更新的值</param>
        /// <param name="wheres">自定义的条件</param>
        /// <param name="whereparameters">自定义条件中的参数</param>
        /// <param name="checkfield">是否检查参数与表结构一致，若检查，则会将不一致的参数移除掉</param>
        /// <returns></returns>
        public override int Update(List<string> tablenames, List<Dictionary<string, dynamic>> values, List<string> wheres, List<Dictionary<string, dynamic>> whereparameters, bool checkfield = true)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString)) throw new Exception("连接字符串不能为空。");
            if (tablenames == null || tablenames.Count == 0) throw new Exception("数据表名称不能为空。");
            if (values != null && tablenames.Count != values.Count) throw new Exception("数据表与记录总数不一致。");
            bool isAutoTran = (Transaction == null);
            if (isAutoTran) CreateTransaction();
            int n = 0;
            string sql = "";
            Dictionary<string, DataTable> outFields = null;
            if (checkfield)
            {
                outFields = new Dictionary<string, DataTable>();
                foreach (string s in tablenames)
                {
                    outFields.Add(s.ToUpper().Trim(), GetTableFields(s));
                }
            }
            try
            {
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OleDbCommand cmd = new OleDbCommand() { Connection = (this.Connection as OleDbConnection), CommandType = CommandType.Text })
                {
                    cmd.Transaction = Transaction as OleDbTransaction;
                    for (int i = 0; i < tablenames.Count; i++)
                    {
                        DataTable dt = null;
                        if (outFields != null) dt = outFields[tablenames[i].Trim().ToUpper()];
                        OleDbParameter[] ps = GetUpdateValues(tablenames[i], values[i], out sql, dt);
                        if (ps == null || ps.Length == 0) continue;
                        if (!string.IsNullOrEmpty(wheres[i]) && !string.IsNullOrWhiteSpace(wheres[i]))
                            sql += " where " + wheres[i];
                        OleDbParameter[] pw = ConvertParameter(whereparameters[i]);
                        if (pw != null && pw.Length > 0)
                        {
                            ps = ps.Union(pw).ToArray();
                        }            
                        cmd.Parameters.AddRange(ps);
                        cmd.CommandText = sql;
                        n += cmd.ExecuteNonQuery();
                        cmd.Parameters.Clear();
                    }
                }
                return n;
            }
            catch (Exception ex)
            {
                Message = "当前执行的Sql：" + sql;
                if (isAutoTran) RollbackTransaction();
                throw ex;
            }
            finally
            {
                if (isAutoTran) CommitTransaction();
                if (this.AutoClosed && Transaction == null)
                {
                    if (this.Connection != null)
                        this.Connection.Close();
                }
            }
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="where">要删除的条件值，以等于判断。为空时表示删除所有的数据</param>
        /// <returns></returns>
        public override int Delete(string tablename, Dictionary<string, dynamic> where = null)
        {
            string sw = "";
            OleDbParameter[] ps = GetUpdateWheres(where, out sw, null);
            string sql = "delete from " + tablename + " " + sw;
            return ExecuteNonQueryParameter(sql, ps);
        }

        /// <summary>
        /// 更新数据表
        /// </summary>
        /// <param name="datatable">要更新的数据表</param>
        /// <param name="sql">获取datatable的Sql语句</param>
        /// <param name="parameters">sql中的参数</param>
        /// <returns></returns>
        public int UpdateDataTable(DataTable datatable, string sql, Dictionary<string, dynamic> parameters = null)
        {
            return UpdateDataTableParameter(datatable, sql, ConvertParameter(parameters));
        }

        /// <summary>
        /// 更新数据表
        /// </summary>
        /// <param name="datatable">要更新的数据表</param>
        /// <param name="sql">获取datatable的Sql语句</param>
        /// <param name="parameters">sql中的参数</param>
        /// <returns></returns>
        public int UpdateDataTableParameter(DataTable datatable, string sql, OleDbParameter[] parameters = null)
        {
            if (string.IsNullOrEmpty(ConnectionString) || string.IsNullOrWhiteSpace(ConnectionString))
                throw new Exception("连接字符串不能为空。");
            if (datatable == null) return 0;
            try
            {
                if (parameters != null && parameters.Length > 0)
                    ConvertParamater(ref sql, ref parameters);
                if (this.Connection == null) this.Connection = new OleDbConnection(ConnectionString);
                if (this.Connection.State != ConnectionState.Open) this.Connection.Open();
                using (OleDbDataAdapter da = new OleDbDataAdapter(sql, (this.Connection as OleDbConnection)))
                {
                    if (parameters != null && parameters.Length > 0)
                        da.SelectCommand.Parameters.AddRange(parameters);
                    OleDbCommandBuilder builder = new OleDbCommandBuilder(da);
                    try
                    {
                        da.UpdateCommand = builder.GetUpdateCommand(true);
                    }
                    catch(Exception ex)
                    {
                        Message = ex.Message;
                    }
                    try
                    {
                        da.DeleteCommand = builder.GetDeleteCommand(true);
                    }
                    catch (Exception ex)
                    {
                        Message = ex.Message;
                    }
                    {
                    }
                    try
                    {
                        da.InsertCommand = builder.GetInsertCommand(true);
                    }
                    catch (Exception ex)
                    {
                        Message = ex.Message;
                    }
                    {
                    }

                    int num = da.Update(datatable);
                    return num;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.AutoClosed)
                {
                    if (this.Connection != null)
                    {
                        this.Connection.Close();
                    }
                }
            }
        }

        /// <summary>
        /// 删除数据表
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <returns></returns>
        public void DeleteTable(string tablename)
        {
            if (string.IsNullOrEmpty(tablename) || string.IsNullOrWhiteSpace(tablename)) throw new Exception("数据表名称不能为空");
            ExecuteNonQueryParameter(" drop table " + tablename);
        }

        /// <summary>
        /// 删除字段
        /// </summary>
        /// <param name="tablename">表名</param>
        /// <param name="fieldName">字段名称</param>
        public void DeleteField(string tablename,string fieldName)
        {
            if (string.IsNullOrEmpty(tablename) || string.IsNullOrWhiteSpace(tablename)) throw new Exception("数据表名称不能为空");
            if (string.IsNullOrEmpty(fieldName) || string.IsNullOrWhiteSpace(fieldName)) throw new Exception("字段名称不能为空");
            ExecuteNonQueryParameter("alter table " + tablename+ " drop column "+ fieldName);
        }

        /// <summary>
        /// 压缩数据库。
        /// 在删除数据表时数据库的空间不会自动回收，导致数据库越来越大，因此需要经过压缩进行回收数据库的空间
        /// </summary>
        public void Compact()
        {
            try
            {
                //int split = dataSource.LastIndexOf("\\") + 1;
                //string path = dataSource.Substring(0, split);
                //string fileName = dataSource.Substring(split, dataSource.Length - split);
                //split = fileName.LastIndexOf(".");
                //string tmpDataSource = path + fileName.Substring(0, split) + "$" + fileName.Substring(split, fileName.Length - split);
                //if (File.Exists(tmpDataSource))
                //{
                //    File.Delete(tmpDataSource);
                //}
                //string argConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + dataSource;
                //string argTmpConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + tmpDataSource;
                //object[] args = new object[] { argConnectionString, argTmpConnectionString };
                //object objJRO = Activator.CreateInstance(Type.GetTypeFromProgID("JRO.JetEngine"));
                //objJRO.GetType().InvokeMember("CompactDatabase", BindingFlags.InvokeMethod, null, objJRO, args);
                //File.Delete(dataSource);
                //File.Move(tmpDataSource, dataSource);
                //Marshal.ReleaseComObject(objJRO);
                //objJRO = null;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 根据参数获取新增的Sql语句与对应的参数
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="parameters">要新增的值</param>
        /// <param name="sql">返回的更新语句</param>
        /// <param name="outFields">数据表字段信息</param>
        /// <returns></returns>
        private OleDbParameter[] GetInsertParameters(string tablename, Dictionary<string, dynamic> parameters, out string sql, DataTable outFields)
        {
            sql = "";
            string values = "";
            List<OleDbParameter> ps = new List<OleDbParameter>();
            foreach (KeyValuePair<string, dynamic> kvp in parameters)
            {
                OleDbParameter p = ConvertParameter(kvp.Key, kvp.Value);
                p.ParameterName = p.ParameterName.Replace(":", "@");
                if (!p.ParameterName.StartsWith("@"))
                    p.ParameterName = "@" + p.ParameterName;
                if (outFields != null && outFields.Rows.Count > 0)
                {
                    DataRow[] drs = outFields.Select("Column_Name='" + p.ParameterName.Replace("@", "").Trim().ToUpper() + "'");
                    if (drs == null || drs.Length == 0) continue;
                }
                values += "," + p.ParameterName;
                ps.Add(p);
            }
            if (string.IsNullOrEmpty(values) || string.IsNullOrWhiteSpace(values))
            {
                Message = "指定参数的名称都不在数据表中";
                return null;
            }
            values = values.Trim().Substring(1);
            sql = "insert into " + tablename + " ( " + values.Replace("@", "");
            sql += " )values(" + values + ") ";
            return ps.ToArray();
        }

        /// <summary>
        /// 获取更新的参数与Sql语句
        /// </summary>
        /// <param name="tablename">数据表名称</param>
        /// <param name="updatevalues">要更新的值</param>
        /// <param name="sql">返回的Sql语句</param>
        /// <param name="outFields">数据表字段信息</param>
        /// <returns></returns>
        private OleDbParameter[] GetUpdateValues(string tablename, Dictionary<string, dynamic> updatevalues, out string sql, DataTable outFields)
        {
            sql = "";
            string values = "";
            List<OleDbParameter> ps = new List<OleDbParameter>();
            foreach (KeyValuePair<string, dynamic> kvp in updatevalues)
            {
                OleDbParameter p = ConvertParameter(kvp.Key, kvp.Value);
                p.ParameterName = p.ParameterName.Replace(":", "@");
                if (!p.ParameterName.StartsWith("@"))
                    p.ParameterName = "@" + p.ParameterName;
                if (outFields != null && outFields.Rows.Count > 0)
                {
                    DataRow[] drs = outFields.Select("Column_Name='" + p.ParameterName.Replace("@", "").Trim().ToUpper() + "'");
                    if (drs == null || drs.Length == 0) continue;
                }
                values += "," + kvp.Key.Replace(":", "").Replace("@", "").Trim() + "=" + p.ParameterName;
                ps.Add(p);
            }
            if (string.IsNullOrEmpty(values) || string.IsNullOrWhiteSpace(values))
            {
                Message = "指定参数的名称都不在数据表中";
                return null;
            }
            values = values.Trim().Substring(1);
            sql = "update " + tablename + " set " + values;            
            return ps.ToArray();
        }

        /// <summary>
        /// 获取更新的条件参数信息与条件语句
        /// </summary>
        /// <param name="wheres">条件参数</param>
        /// <param name="where">返回的条件语句</param>
        /// <param name="outFields">数据表字段信息</param>
        /// <returns></returns>
        private OleDbParameter[] GetUpdateWheres(Dictionary<string, dynamic> wheres, out string where, DataTable outFields)
        {
            where = "";
            if (wheres == null || wheres.Count == 0) return null;
            List<OleDbParameter> ps = new List<OleDbParameter>();
            int i = 0;
            foreach (KeyValuePair<string, dynamic> kvp in wheres)
            {
                OleDbParameter p = ConvertParameter(kvp.Key, kvp.Value);
                p.ParameterName = p.ParameterName.Replace(":", "@");
                if (!p.ParameterName.StartsWith("@"))
                    p.ParameterName = "@" + p.ParameterName;
                if (outFields != null && outFields.Rows.Count > 0)
                {
                    DataRow[] drs = outFields.Select("Column_Name='" + p.ParameterName.Replace("@", "").Trim().ToUpper() + "'");
                    if (drs == null || drs.Length == 0) continue;
                }
                p.ParameterName = p.ParameterName + "_" + i.ToString();
                where += " and " + kvp.Key.Replace(":", "").Replace("@", "").Trim() + "=" + p.ParameterName;
                
                ps.Add(p);
                i++;
            }
            if (!string.IsNullOrEmpty(where) && !string.IsNullOrWhiteSpace(where))
                where = " where " + where.Trim().Substring(3);
            return ps.ToArray();
        }

        /// <summary>
        /// 转换参数与Sql语句
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="parameters"></param>
        private void ConvertParamater(ref string sql, ref OleDbParameter[] parameters)
        {
            if (sql.ToLower().Contains("to_date("))
            {
                string[] temps = sql.Split(new string[] { "to_date(", "TO_DATE(" }, StringSplitOptions.RemoveEmptyEntries);
                int cnt = temps.Length;
                string s = temps[0];
                for (int i = 1; i < cnt; i++)
                {
                    string tmp = temps[i];
                    int idx = tmp.IndexOf(')');
                    string tmp1 = tmp.Substring(0, idx);
                    string tmp2 = tmp.Substring(idx + 1);
                    tmp1 = tmp1.Split(',')[0].Trim();
                    tmp1 = tmp1.Replace("'", "#");
                    //tmp1 = tmp1.Insert(1, "#");
                    //tmp1 = tmp1.Insert(tmp1.Length - 1, "#");
                    s += " " + tmp1 + " " + tmp2;
                }
                sql = s;
            }            
            if (parameters == null || parameters.Length == 0) return;
            sql = sql.ToLower().Trim();
            Dictionary<OleDbParameter, int> ps = new Dictionary<OleDbParameter, int>();
            foreach (OleDbParameter o in parameters)
            {
                o.ParameterName = o.ParameterName.ToLower().Trim();
                if (o.ParameterName.StartsWith("@"))
                {
                    //不处理
                }
                else if (o.ParameterName.StartsWith(":")) o.ParameterName = o.ParameterName.Replace(":", "@");
                else o.ParameterName = "@" + o.ParameterName;
                string str = o.ParameterName;
                sql = sql.Replace(str.Replace("@", ":"), o.ParameterName);
                int idx = sql.IndexOf(o.ParameterName);
                ps.Add(o, idx);
            }
            List<OleDbParameter> newps = new List<OleDbParameter>();
            var sort = from itm in ps orderby itm.Value ascending select itm;
            foreach (KeyValuePair<OleDbParameter, int> kvp in sort)
            {
                newps.Add(kvp.Key);
            }
            parameters = newps.ToArray();
        }

        /// <summary>
        /// 将自定义的参数转为标准的参数
        /// </summary>
        /// <param name="parameters">自定义参数</param>
        /// <returns></returns>
        private OleDbParameter[] ConvertParameter(Dictionary<string, dynamic> parameters)
        {
            if (parameters == null || parameters.Count == 0) return null;
            List<OleDbParameter> ps = new List<OleDbParameter>();
            foreach (KeyValuePair<string, dynamic> kvp in parameters)
            {
                ps.Add(ConvertParameter(kvp.Key.Trim(), kvp.Value));
            }
            return ps.ToArray();
        }

        /// <summary>
        /// 将单个参数转成标准的参数
        /// </summary>
        /// <param name="name">参数名称</param>
        /// <param name="value">参数值</param>
        /// <returns></returns>
        private OleDbParameter ConvertParameter(string name, dynamic value)
        {
            OleDbParameter p = new OleDbParameter()
            {
                ParameterName = name.Trim(),
                Value = (value == null || string.IsNullOrWhiteSpace(value.ToString())) ? DBNull.Value : value
            };
            if (p.Value != null && p.Value is DateTime)
            {
                p.DbType = DbType.Date;
            }
            return p;
        }

        /// <summary>
        /// 析构函数
        /// </summary>
        ~AccessContext()
        {
            try
            {
                Close();
            }
            catch
            {

            }
        }


    }

    /// <summary>
    /// ACCESS数据类型
    /// </summary>
    public enum zjgisMdbFieldType
    {
        /// <summary>
        /// 二进制存储模式
        /// </summary>
        BINARY,
        /// <summary>
        /// 布尔类型
        /// </summary>
        BIT,
        /// <summary>
        /// 0-255间的整数
        /// </summary>
        TINYINT,
        /// <summary>
        /// 
        /// </summary>
        MONEY,
        /// <summary>
        /// 100年-9999年间的日期时间类型
        /// </summary>
        DATETIME,
        /// <summary>
        /// 用于远程过程调用的唯一标识数字
        /// </summary>
        UNIQUEIDENTIFIER,
        /// <summary>
        /// 单精度浮点值
        /// </summary>
        REAL,
        /// <summary>
        /// 双精度浮点值
        /// </summary>
        FLOAT,
        /// <summary>
        /// 介于-32,768和32,767之间的短整型
        /// </summary>
        SMALLINT,
        /// <summary>
        /// 介于-2,147,483,648和2,147,483,647之间的长整型
        /// </summary>
        INTEGER,
        /// <summary>
        /// 精确的数字数据类型
        /// </summary>
        DECIMAL,
        /// <summary>
        /// 从零到最大值2.14吉字节（备注）
        /// </summary>
        TEXT,
        /// <summary>
        /// 从零到最大值2.14吉字节。用于 OLE 对象
        /// </summary>
        IMAGE,
        /// <summary>
        /// 从零到255个字符（文本）
        /// </summary>
        CHARACTER
    }
}
